﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Web;
using System.IO;
using System.Collections;
using RKLib.ExportData;

namespace Mammoo.File
{
    public static class ExcelHelper
    {
        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="dt">需要导出的Excel</param>
        /// <param name="fileName">导出文件名</param>
        public static void ExportToExcel(DataTable dt, string fileName)
        {
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
            Export objExport = new Export("Web");
            objExport.ExportDetails(dt, Export.ExportFormat.Excel, fileName);
        }

        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="dt">需要导出的Excel</param>
        /// <param name="fileName">导出文件名</param>
        /// <param name="columnList">需要导出DataTable中的列索引,用数组表示</param>
        public static void ExportToExcel(DataTable dt, string fileName, int[] columnList)
        {
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
            Export objExport = new Export("Web");
            objExport.ExportDetails(dt, columnList, Export.ExportFormat.Excel, fileName);
        }

        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="dt">需要导出的Excel</param>
        /// <param name="fileName">导出文件名</param>
        /// <param name="headerList">导出到Excel中的自定义列名,数量要跟DataTable中的列的数量一致</param>
        public static void ExportToExcel(DataTable dt, string fileName, string[] headerList)
        {
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
            Export objExport = new Export("Web");

            int[] columnList = new int[dt.Columns.Count];
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                columnList[i] = i;
            }

            objExport.ExportDetails(dt, columnList, headerList, Export.ExportFormat.Excel, fileName);
        }

        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="dt">需要导出的Excel</param>
        /// <param name="fileName">导出文件名</param>
        /// <param name="columnList">需要导出DataTable中的列索引,用数组表示</param>
        /// <param name="headerList">导出到Excel中的自定义列名,数量要跟DataTable中的列的数量一致</param>
        public static void ExportToExcel(DataTable dt, string fileName, int[] columnList, string[] headerList)
        {
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
            Export objExport = new Export("Web");
            objExport.ExportDetails(dt, columnList, headerList, Export.ExportFormat.Excel, fileName);
        }






        /// <summary> 
        /// 获取指定路径、指定工作簿名称的Excel数据:取第一个sheet的数据 
        /// </summary> 
        /// <param name="FilePath">文件存储路径</param> 
        /// <param name="WorkSheetName">工作簿名称</param> 
        /// <returns>如果争取找到了数据会返回一个完整的Table，否则返回异常</returns> 
        public static DataTable GetExcelData(string astrFileName)
        {
            string strSheetName = GetExcelWorkSheets(astrFileName)[0].ToString();
            return GetExcelData(astrFileName, strSheetName);
        }

        /// <summary> 
        /// 获取指定路径、指定工作簿名称的Excel数据 
        /// </summary> 
        /// <param name="FilePath">文件存储路径</param> 
        /// <param name="WorkSheetName">工作簿名称</param> 
        /// <returns>如果争取找到了数据会返回一个完整的Table，否则返回异常</returns> 
        public static DataTable GetExcelData(string FilePath, string WorkSheetName)
        {
            DataTable dtExcel = new DataTable();
            OleDbConnection con = new OleDbConnection(GetExcelConnection(FilePath));
            OleDbDataAdapter adapter = new OleDbDataAdapter("Select * from [" + WorkSheetName + "$]", con);
            //读取 
            con.Open();
            adapter.FillSchema(dtExcel, SchemaType.Mapped);
            adapter.Fill(dtExcel);
            con.Close();
            dtExcel.TableName = WorkSheetName;
            //返回 
            return dtExcel;
        }

        /// <summary> 
        /// 返回指定文件所包含的工作簿列表;如果有WorkSheet，就返回以工作簿名字命名的ArrayList，否则返回空 
        /// </summary> 
        /// <param name="strFilePath">要获取的Excel</param> 
        /// <returns>如果有WorkSheet，就返回以工作簿名字命名的ArrayList，否则返回空</returns> 
        public static ArrayList GetExcelWorkSheets(string strFilePath)
        {
            ArrayList alTables = new ArrayList();
            OleDbConnection odn = new OleDbConnection(GetExcelConnection(strFilePath));
            odn.Open();
            DataTable dt = new DataTable();
            dt = odn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dt == null)
            {
                throw new Exception("无法获取指定Excel的架构。");
            }
            foreach (DataRow dr in dt.Rows)
            {
                string tempName = dr["Table_Name"].ToString();
                int iDolarIndex = tempName.IndexOf('$');
                if (iDolarIndex > 0)
                {
                    tempName = tempName.Substring(0, iDolarIndex);
                }
                //修正了Excel2003中某些工作薄名称为汉字的表无法正确识别的BUG。 
                if (tempName[0] == '\'')
                {
                    if (tempName[tempName.Length - 1] == '\'')
                    {
                        tempName = tempName.Substring(1, tempName.Length - 2);
                    }
                    else
                    {
                        tempName = tempName.Substring(1, tempName.Length - 1);
                    }
                }
                if (!alTables.Contains(tempName))
                {
                    alTables.Add(tempName);
                }
            }
            odn.Close();
            if (alTables.Count == 0)
            {
                return null;
            }
            return alTables;
        }

        /// <summary> 
        /// 获取链接字符串 
        /// </summary> 
        /// <param name="strFilePath"></param> 
        /// <returns></returns> 
        public static string GetExcelConnection(string strFilePath)
        {
            if (!System.IO.File.Exists(strFilePath))
            {
                throw new Exception("指定的Excel文件不存在！");
            }
            return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Extended properties=\"Excel 8.0;Imex=1;HDR=Yes;\"";
            //@"Provider=Microsoft.Jet.OLEDB.4.0;" + 
            //@"Data Source=" + strFilePath + ";" + 
            //@"Extended Properties=" + Convert.ToChar(34).ToString() + 
            //@"Excel 8.0;" + "Imex=1;HDR=Yes;" + Convert.ToChar(34).ToString(); 
        }
    }
}
